﻿Imports MySql.Data.MySqlClient
Imports System.Text
Class MedicoManager
    Dim md As Medico = New Medico()
    Dim cmd As New MySqlCommand
    Dim dbc As New DBCommand
    Public Sub New(ByVal medico As Medico)
        Me.md = medico
    End Sub
    Public Sub New()

    End Sub

    Public Sub InsertMedico()
        Dim query As New StringBuilder
        query.Append("Insert into Medico(idmedico,codicefiscale, cognome, nome, datanascita, sesso, indirizzo, citta, cap, provincia, specializzazione, telefono1, telefono2, email) VALUES(")
        query.Append(md.codiceMedico)
        query.Append(",")
        query.Append("'")
        query.Append(md.codiceFiscale)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.cognome)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.nome)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.dataNascita)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.sex)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.indirizzo)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.citta)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.cap)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.provincia)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.specializzazione)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.telefono1)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.telefono2)
        query.Append("'")
        query.Append(",")
        query.Append("'")
        query.Append(md.email)
        query.Append("'")
        query.Append(")")
        cmd.CommandText = query.ToString
        cmd.Connection = dbc.connect()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As MySqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try

        dbc.disconnect()

    End Sub

    Public Sub UpdateMedico(ByVal id As String)
        'TODO: query di update
        Dim query As New StringBuilder
        query.Append("Update medico set ")
        query.Append("cognome='")
        query.Append(md.cognome)
        query.Append("', ")
        query.Append("nome='")
        query.Append(md.nome)
        query.Append("', ")
        query.Append("datanascita='")
        query.Append(md.dataNascita)
        query.Append("', ")
        query.Append("sesso='")
        query.Append(md.sex)
        query.Append("', ")
        query.Append("indirizzo='")
        query.Append(md.indirizzo)
        query.Append("', ")
        query.Append("citta='")
        query.Append(md.citta)
        query.Append("', ")
        query.Append("cap='")
        query.Append(md.cap)
        query.Append("', ")
        query.Append("provincia='")
        query.Append(md.provincia)
        query.Append("', ")
        query.Append("specializzazione='")
        query.Append(md.specializzazione)
        query.Append("', ")
        query.Append("telefono1='")
        query.Append(md.telefono1)
        query.Append("', ")
        query.Append("telefono2='")
        query.Append(md.telefono2)
        query.Append("', ")
        query.Append("email='")
        query.Append(md.email)
        query.Append("', ")
        query.Append("codicefiscale='")
        query.Append(md.codiceFiscale)
        query.Append("' where idMedico='")
        query.Append(id)
        query.Append("'")
        cmd.CommandText = query.ToString
        cmd.Connection = dbc.connect

        Try
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Public Function GetMedico(ByVal campo As String, ByVal valore As Object) As List(Of Medico)
        Dim lstMedico As New List(Of Medico)
        Dim query As New StringBuilder
        Dim dr As MySqlDataReader
        query.Append("Select * from Medico where ")
        query.Append(campo)
        query.Append("='")
        query.Append(valore)
        query.Append("'")
        cmd.CommandText = query.ToString
        'dbc.connect()
        Try
            dr = cmd.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
        While dr.Read
            Dim tmpMed As New Medico
            tmpMed.codiceMedico = dr("id")
            tmpMed.cap = dr("cap")
            tmpMed.citta = dr("citta")
            tmpMed.codiceFiscale = dr("codicefiscale")
            tmpMed.cognome = dr("cognome")
            tmpMed.dataNascita = dr("datanascita")
            tmpMed.email = dr("email")
            tmpMed.indirizzo = dr("indirizzo")
            tmpMed.nome = dr("nome")
            tmpMed.provincia = dr("provincia")
            tmpMed.sex = dr("sesso")
            tmpMed.specializzazione = dr("specializzazione")
            tmpMed.telefono1 = dr("telefono1")
            tmpMed.telefono2 = dr("telefono2")
            lstMedico.Add(tmpMed)
        End While
        Return lstMedico
    End Function
    Public Function GetMedico(ByVal id As String) As Medico
        Dim tmpMed As New Medico
        Dim query As New StringBuilder
        Dim dr As MySqlDataReader
        'Dim mdr As MySqlDataReader
        query.Append("Select * from Medico where idMedico='")
        query.Append(id)
        query.Append("'")
        cmd.CommandText = query.ToString
        cmd.Connection = dbc.connect()
        Try
            dr = cmd.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
        If dr.HasRows Then
            dr.Read()
            tmpMed.codiceMedico = dr("idmedico")
            tmpMed.cap = dr("cap")
            tmpMed.citta = dr("citta")
            tmpMed.codiceFiscale = dr("codicefiscale")
            tmpMed.cognome = dr("cognome")
            tmpMed.dataNascita = dr("datanascita")
            tmpMed.email = dr("email")
            tmpMed.indirizzo = dr("indirizzo")
            tmpMed.nome = dr("nome")
            tmpMed.provincia = dr("provincia")
            tmpMed.sex = dr("sesso")
            tmpMed.specializzazione = dr("specializzazione")
            tmpMed.telefono1 = dr("telefono1")
            tmpMed.telefono2 = dr("telefono2")
        End If
        dr.Close()
        dbc.disconnect()

        Return tmpMed
    End Function

    Public Function GetMedici() As List(Of Medico)
        Dim lstMedico As New List(Of Medico)
        Dim query As New StringBuilder
        Dim dr As MySqlDataReader
        query.Append("Select * from Medico")
        cmd.CommandText = query.ToString
        dbc.connect()
        Try
            dr = cmd.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
        While dr.Read
            Dim tmpMed As New Medico
            tmpMed.codiceMedico = dr("id")
            tmpMed.cap = dr("cap")
            tmpMed.citta = dr("citta")
            tmpMed.codiceFiscale = dr("codicefiscale")
            tmpMed.cognome = dr("cognome")
            tmpMed.dataNascita = dr("datanascita")
            tmpMed.email = dr("email")
            tmpMed.indirizzo = dr("indirizzo")
            tmpMed.nome = dr("nome")
            tmpMed.provincia = dr("provincia")
            tmpMed.sex = dr("sesso")
            tmpMed.specializzazione = dr("specializzazione")
            tmpMed.telefono1 = dr("telefono1")
            tmpMed.telefono2 = dr("telefono2")
            lstMedico.Add(tmpMed)
        End While
        Return lstMedico
    End Function

    Public Sub DeleteMedico(ByVal id As String)
        Dim query As New StringBuilder
        query.Append("delete from Medico where idMedico='")
        query.Append(id)
        query.Append("'")
        cmd.CommandText = query.ToString
        cmd.Connection = dbc.connect()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As MySqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try

        dbc.disconnect()
    End Sub

    Public Function CalcolaCodiceMedico() As Integer  'prende il primo id disponibile
        Dim query As New StringBuilder
        Dim maxid As Integer
        Dim dr As MySqlDataReader
        query.Append("select max(idMedico) from Medico")
        cmd.CommandText = query.ToString
        cmd.Connection = dbc.connect
        Try
            dr = cmd.ExecuteReader()
            dr.Read()
            If dr.Item(0) Is DBNull.Value Then
                maxid = 1
            Else
                maxid = dr.Item(0) + 1
            End If
        Catch ex As Exception
            dr.Close()
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
        dr.Close()
        Return maxid
    End Function

End Class

